package mwa.cs.dao;
import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.Request;

public class RequestDAO {

	private Database db;
    /**
     * Instantiate a new object of this class
     * @param database a database
     */
    public RequestDAO(Database db) {
        this.db = db;
    }
    
    //
    public Request findRequestByRequestNumber(int pnSiteId, String paRequestNumber ) {
    	String vaSql = "SELECT * FROM request WHERE site_id = ? AND request_number = ? AND status = 1  AND request_group = 0 AND request_type NOT IN 01 ";
    	Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paRequestNumber);
        if (result != null) {
        	Request voRequest = new Request();
        	voRequest.setVaRequestNumber( (String) result.get("REQUEST_NUMBER"));
        	voRequest.setVaAccount( (String) result.get("ACCOUNT"));
        	voRequest.setVaPName( (String) result.get("PNAME"));
        	voRequest.setVaFName( (String) result.get("FNAME"));
        	voRequest.setVaLName( (String) result.get("LNAME"));
        	voRequest.setVaAddress( (String) result.get("ADDRESS"));
        	voRequest.setVaMeterSize( (String) result.get("METER_SIZE"));
        	voRequest.setVaApplicantName( (String) result.get("APPLICANT_NAME"));
        	voRequest.setVaContactHomeNumber( (String) result.get("CONTACT_HOME_NUMBER"));
        	voRequest.setVaContactAddress( (String) result.get("CONTACT_ADDRESS"));
        	voRequest.setVaContactCityCode( (String) result.get("CONTACT_CITY_CODE"));
        	voRequest.setVaContactDistrictCode( (String) result.get("CONTACT_DISTRICT_CODE"));
        	voRequest.setVaContactProvinceCode( (String) result.get("CONTACT_PROVINCE_CODE"));
        	voRequest.setVaContactPostCode( (String) result.get("CONTACT_POST_CODE") );
        	voRequest.setVaContactTelephone( (String) result.get("CONTACT_TELEPHONE") );
        	voRequest.setVaRequestType( (String) result.get("REQUEST_TYPE"));
        	voRequest.setVaDescription( (String) result.get("DESCRIPTION"));        	
            return voRequest;
        } else {
            return null;
        }
    }
    
    
 // เพิ่ม request
    public void insertRequest(Request poRequest){
    	String vaSql = "INSERT INTO request(site_id, request_number, request_group, request_type, applicant_name, applicant_home_number, "
    			+ "applicant_address, applicant_city_code, applicant_district_code, applicant_province_code, applicant_post_code, "
    			+ "applicant_telephone, description, account, pname, fname, lname, card_id, house_id, home_number, address, city_code, "
    			+ "district_code, province_code, post_code, telephone, contact_name, contact_home_number, contact_address, contact_city_code, "
    			+ "contact_district_code, contact_province_code, contact_post_code, contact_telephone, allowance, wastewater_charge, garbage_charge, "
    			+ "other_charge, document_type, document_reference, customer_class, customer_type, meter_size, meter_brand, meter_serial_number, "
    			+ "meter_number, meter_install_date, meter_date, status, reason_cancel, reason_close, insert_user_id, insert_date, insert_time, "
    			+ "update_user_id, update_date, update_time) "
    			+ "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
    			+ "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    	try {
            db.insert(vaSql,
            		poRequest.getVnSiteId(),
            		poRequest.getVaRequestNumber(),
            		poRequest.getVaRequestGroup(),
            		poRequest.getVaRequestType(),
            		poRequest.getVaApplicantName(),
            		poRequest.getVaApplicantHomeNumber(),
            		poRequest.getVaApplicantAddress(),
            		poRequest.getVaApplicantCityCode(),
            		poRequest.getVaApplicantDistrictCode(),
            		poRequest.getVaApplicantProvinceCode(),
            		poRequest.getVaApplicantPostCode(),
            		poRequest.getVaApplicantTelephone(),
            		poRequest.getVaDescription(),
            		poRequest.getVaAccount(),
            		poRequest.getVaPName(),
            		poRequest.getVaFName(),
            		poRequest.getVaLName(),
            		poRequest.getVaCardId(),
            		poRequest.getVaHouseId(),
            		poRequest.getVaHomeNumber(),
            		poRequest.getVaAddress(),
            		poRequest.getVaCityCode(),
            		poRequest.getVaDistrictCode(),
            		poRequest.getVaProvinceCode(),
            		poRequest.getVaPostCode(),
            		poRequest.getVaTelephone(),
            		poRequest.getVaContactName(),
            		poRequest.getVaContactHomeNumber(),
            		poRequest.getVaContactAddress(),
            		poRequest.getVaContactCityCode(),
            		poRequest.getVaContactDistrictCode(),
            		poRequest.getVaContactProvinceCode(),
            		poRequest.getVaContactPostCode(),
            		poRequest.getVaContactTelephone(),
            		poRequest.getVaAllowance(),
            		poRequest.getVnWastewaterCharge(),
            		poRequest.getVnGarbageCharge(),
            		poRequest.getVnOtherCharge(),
            		poRequest.getVaDocumentType(),
            		poRequest.getVaDocumentReference(),
            		poRequest.getVaCustomerClass(),
            		poRequest.getVaCustomerType(),
            		poRequest.getVaMeterSize(),
            		poRequest.getVaMeterBrand(),
            		poRequest.getVaMeterSerialNumber(),
            		poRequest.getVaMeterNumber(),
            		poRequest.getVaMeterInstallDate(),
            		poRequest.getVaMeterDate(),
            		poRequest.getVaStatus(),
            		poRequest.getVaReasonCancel(),
            		poRequest.getVaReasonClose(),
            		poRequest.getVnInsertUserId(),
            		poRequest.getVaInsertDate(),
            		poRequest.getVaInsertTime(),
            		poRequest.getVnUpdateUserId(),
            		poRequest.getVaUpdateDate(),
            		poRequest.getVaUpdateTime()
            );
        } catch (Exception e) {
        	e.printStackTrace();
        	throw new RuntimeException(e);
        }
    }

    // ดึงข้อมูล คำร้อง เพื่อยกเลิกหรือปิดเลขที่ใบคำร้อง 
  	public Map<String, Object> getRequestForCloseOrCancel(int pnSiteId, String paRequestNumber) {
  		String vaSql = "SELECT request_number, applicant_name, request_group, request_type, NVL(contact_home_number,' ') AS contact_home_number, NVL(contact_address,' ') AS contact_address, "
  				+ "NVL(contact_city_code,' ') AS contact_city_code, NVL(contact_district_code,' ') AS contact_district_code,"
  				+ " NVL(contact_province_code,' ') AS contact_province_code, NVL(contact_post_code,' ') AS contact_post_code, "
  				+ " NVL(contact_telephone,' ') AS contact_telephone, description , status"
  				+ " FROM request "
  				+ " WHERE site_id = ? AND request_number = ? AND status not in ('3','4')";
  		Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paRequestNumber);
  		if (result != null) {
  			return result;
  		} else {
  			return null;
  		}
  	}
  	
  	// แก้ไขข้อมูล Request สำหรับยกเลิกหรือปิดเลขที่ใบคำร้อง
	public boolean updateRequestForCloseOrCancel(Request poNewRequest) {
        String vaSql = "UPDATE request SET "
      		  + "description = ?, status = ?, reason_close = ?, reason_cancel = ?, "
      		  + "update_user_id = ?, update_date = ?, update_time = ? "
      		  + "WHERE request_number = ?";
      
        boolean result = false;
        try {
        	db.update(vaSql,
        			poNewRequest.getVaDescription(),
        			poNewRequest.getVaStatus(),
        			poNewRequest.getVaReasonClose(),
        			poNewRequest.getVaReasonCancel(),
        			poNewRequest.getVnUpdateUserId(),
        			poNewRequest.getVaUpdateDate(),
        			poNewRequest.getVaUpdateTime(),
        			poNewRequest.getVaRequestNumber()
        			);
        	result = true;
        } catch (Exception e) {
        	System.out.println(e);
        	result = false;
        }
        return result;
	}
 	
    // ดึงข้อมูล คำร้อง เพื่อบันทึกผลการติดตั้งประปา
   	public Map<String, Object> getRequestForInstall(int pnSiteId, String paRequestNumber) {
   		String vaSql = "SELECT rq.account, cusc.description, rq.pname || ' ' || rq.fname || ' ' || rq.lname AS full_name, "
   				+ "rq.meter_size, ms.description AS description_MeterSize, rq.status "
   				+ "FROM request rq "
   				+ "INNER JOIN customer_class cusc ON rq.customer_class = cusc.code "
   				+ "INNER JOIN meter_size ms ON rq.meter_size = ms.meter_size_code "
   				+ "WHERE rq.site_id = ? AND rq.request_number = ? AND rq.request_group = '0' AND rq.request_type = '01' "
   				+ "AND ms.request_type = '**'";
   		Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paRequestNumber);
   		if (result != null) {
   			return result;
   		} else {
   			return null;
   		}
   	}
    	
   	// แก้ไขข้อมูล Request เพื่อแก้ไขผลการติดตั้งประปา
	public boolean updateRequestForInstall(Request poNewRequest) {
        String vaSql = "UPDATE request SET "
      		  + "meter_brand = ?, meter_serial_number = ?, meter_number = ?, meter_install_date = ?, meter_date = ?, status = ?, "
      		  + "update_user_id = ?, update_date = ?, update_time = ? "
      		  + "WHERE site_id = ? AND request_number = ?";
        boolean result = false;
        try {
        	db.update(vaSql,
        			poNewRequest.getVaMeterBrand(),
        			poNewRequest.getVaMeterSerialNumber(),
        			poNewRequest.getVaMeterNumber(),
        			poNewRequest.getVaMeterInstallDate(),
        			poNewRequest.getVaMeterDate(),
        			poNewRequest.getVaStatus(),
        			poNewRequest.getVnUpdateUserId(),
        			poNewRequest.getVaUpdateDate(),
        			poNewRequest.getVaUpdateTime(),
        			poNewRequest.getVnSiteId(),
        			poNewRequest.getVaRequestNumber()
        			);
        	result = true;
        } catch (Exception e) {
        	System.out.println(e);
        	result = false;
        }
        return result;
	}
	
	// แก้ไขข้อมูล Request เพื่อแก้ไขผลการยกเลิกใบเสร็จรับเงิน
		public boolean resetRequestForCancelReceive(Request poNewRequest) {
	        String vaSql = "UPDATE request SET "
	      		  + "status = ?, update_user_id = ?, update_date = ?, update_time = ? "
	      		  + "WHERE site_id = ? AND request_number = ?";
	        boolean result = false;
	        try {
	        	db.update(vaSql,
	        			poNewRequest.getVaStatus(),
	        			poNewRequest.getVnUpdateUserId(),
	        			poNewRequest.getVaUpdateDate(),
	        			poNewRequest.getVaUpdateTime(),
	        			poNewRequest.getVnSiteId(),
	        			poNewRequest.getVaRequestNumber()
	        			);
	        	result = true;
	        } catch (Exception e) {
	        	System.out.println(e);
	        	result = false;
	        }
	        return result;
		}
    
   	public void deleteRequest(String paCondition){
    	String vaSql = "DELETE FROM request WHERE "+paCondition;
    	try {
            db.update(vaSql);
        } catch (Exception e) {
        	e.printStackTrace();
        	throw new RuntimeException(e);
        }
    }
   	
   	public Map<String, Object> getRequest(int pnSiteId, String paRequestNumber) {
  		String vaSql = "SELECT * FROM request "
  				+ "WHERE site_id = ? AND request_number = ? AND request_group = 1 AND status = 1";
  		Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paRequestNumber);
  		return result;
  	}
   	
   	public Map<String, Object> getAllRequestAllStatus(int pnSiteId, String paRequestNumber) {
  		String vaSql = "SELECT * FROM request "
  				+ "WHERE site_id = ? AND request_number = ?";
  		Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paRequestNumber);
  		return result;
  	}
   	
   	public Map<String, Object> getRequestByStatus(int pnSiteId, String paRequestNumber , String paStatus) {
  		String vaSql = "SELECT * FROM request "
  				+ "WHERE site_id = ? AND request_number = ? AND status = ? AND REQUEST_GROUP = '0' AND REQUEST_TYPE = '01'";
  		
  		Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paRequestNumber,paStatus);
  		
  		return result;
  	}
   	
    public String getRequestType(int pnSiteId, String paRequestNumber) {
        String vaSql = "SELECT request_type FROM request WHERE site_id = ? AND request_number = ?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paRequestNumber);

        if (result != null) {
            return result.get("REQUEST_TYPE").toString();
        } else {
            return "";
        }
    }
   	
   	// ดึงข้อมูล คำร้อง เพื่อรับเงินค่าอื่นๆ
   	public Map<String, Object> getRequestForReceiveOther(int pnSiteId, String paRequestNumber) {
   		String vaSql = "SELECT rq.request_number, rq.meter_serial_number, rq.account, "
   				+ "substr(rq.insert_date,7,2)||'/'|| substr(rq.insert_date,5,2) ||'/'||substr(rq.insert_date,1,4) AS insert_date, "
   				+ "rt.request_description, rq.pname || ' ' || rq.fname || ' ' || rq.lname AS full_name, "
   				+ "rq.home_number, rq.address, rq.city_code, rq.district_code, rq.province_code, rq.post_code, rq.telephone, "
   				+ "rq.status  "
   				+ "FROM request rq "
   				+ "INNER JOIN request_type rt ON rq.request_type = rt.request_type "
   				+ "WHERE rq.site_id = ? AND rq.request_number = ?";
   		Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paRequestNumber);
   		if (result != null) {
   			return result;
   		} else {
   			return null;
   		}
   	}
   	
   	// แก้ไขข้อมูล Request สำหรับรับเงินค่าอื่นๆ
 	public boolean updateRequestForReceiveOther(Request poNewRequest) {
         String vaSql = "UPDATE request SET "
        	  + "account = ?, "
       		  + "status = ?, "
       		  + "update_user_id = ?, update_date = ?, update_time = ? "
       		  + "WHERE site_id = ? AND request_number = ?";
       
         boolean result = false;
         try {
         	db.update(vaSql,
         			poNewRequest.getVaAccount(),
         			poNewRequest.getVaStatus(),
         			poNewRequest.getVnUpdateUserId(),
         			poNewRequest.getVaUpdateDate(),
         			poNewRequest.getVaUpdateTime(),
         			poNewRequest.getVnSiteId(),
         			poNewRequest.getVaRequestNumber()
         			);
         	result = true;
         } catch (Exception e) {
         	System.out.println(e);
         	result = false;
         }
         return result;
 	}
 	
 	// แก้ไขข้อมูล Request สำหรับรับเงินค่าอื่นๆที่ไม่ใช่ติดตั้งประปาใหม่
 	public boolean updateRequestForReceiveOtherNotNewWater(Request poNewRequest) {
        String vaSql = "UPDATE request SET "
      		  + "status = ?, "
      		  + "update_user_id = ?, update_date = ?, update_time = ? "
      		  + "WHERE site_id = ? AND request_number = ?";
      
        boolean result = false;
        try {
        	db.update(vaSql,
        			poNewRequest.getVaStatus(),
        			poNewRequest.getVnUpdateUserId(),
        			poNewRequest.getVaUpdateDate(),
        			poNewRequest.getVaUpdateTime(),
        			poNewRequest.getVnSiteId(),
        			poNewRequest.getVaRequestNumber()
        			);
        	result = true;
        } catch (Exception e) {
        	System.out.println(e);
        	result = false;
        }
        return result;
	}
 	
    public boolean updateRequestByReplaceOldRequestNumber(Request poRequest,String paRequestNumber){
    	String vaSql = "UPDATE request SET request_number = ?, request_type = ?, applicant_name= ?, description = ?, contact_home_number = ?, contact_address = ?,"
    			+ "contact_city_code = ?, contact_district_code = ?, contact_province_code = ? , contact_post_code = ?, contact_telephone = ?, update_user_id = ?,"
    			+ "update_date = ? , update_time = ?  WHERE site_id = ? AND request_number = ? ";
        boolean result = false;
    	try {
            db.update(vaSql,
            		poRequest.getVaRequestNumber(),
            		poRequest.getVaRequestType(),
            		poRequest.getVaApplicantName(),
            		poRequest.getVaDescription(),
            		poRequest.getVaContactHomeNumber(),
            		poRequest.getVaContactAddress(),
            		poRequest.getVaContactCityCode(),
            		poRequest.getVaContactDistrictCode(),
            		poRequest.getVaContactProvinceCode(),
            		poRequest.getVaContactPostCode(),
            		poRequest.getVaContactTelephone(),
            		poRequest.getVnUpdateUserId(),
            		poRequest.getVaUpdateDate(),
            		poRequest.getVaUpdateTime(),
            		poRequest.getVnSiteId(),
            		paRequestNumber
            		);    
                 	result = true;
                 } catch (Exception e) {
                 	System.out.println(e);
                 	result = false;
                 }
                 return result;
    }
    
    public int getCountAllRequestByRequestNumber(int pnSiteId, String paRequestNumber, String paStartDate, String paToDate, String paStatus) {
   		String vaSql = "SELECT COUNT(*) "
   					+ "FROM request rq "
   					+ "WHERE rq.site_id = " + pnSiteId;
   					if (!paRequestNumber.equals("")) {
   						vaSql += " AND rq.request_number = '" + paRequestNumber + "'";
   					}
   					if (!paStartDate.equals("")) {
   						vaSql += " AND rq.insert_date >= '" + paStartDate + "'";
   					} 
   					if (!paToDate.equals("")) {
   						vaSql += " AND rq.insert_date <= '" + paToDate + "'";
   					} 
   					if (!paStatus.equals("")) {
   						vaSql += " AND rq.status IN (" + paStatus + ")";
   					}
   					Map<String, Object> result = db.querySingle(vaSql);
   			        if (result != null) {
   			        	return Integer.parseInt(result.get("COUNT(*)").toString());
   			        } else {
   			            return 0;
   			        }
   	}
    
   	public List<Map<String, Object>> findRequestByRequestNumber(int siteId, String requestNumber, String startDate, String toDate, String status, String paSortColumn, int pnStart, int pnEnd) {
   		String vaSql = "SELECT substr(rq.insert_date,7,2) || '/' || substr(rq.insert_date,5,2) || '/' || substr(rq.insert_date,1,4) as insert_date, " 
   					+ "rq.request_number, rq.applicant_name, "
   					+ "rt.request_description, CASE rq.status WHEN '1' THEN 'รับคำร้อง' WHEN '2' THEN 'รับเงิน' WHEN '3' THEN 'ปิดคำร้อง' ELSE 'ยกเลิก' END AS status "
   					+ "FROM request rq "
   					+ "INNER JOIN request_type rt ON rq.request_type = rt.request_type "
   					+ "WHERE rq.site_id = " + siteId;
   					if (!requestNumber.equals("")) {
   						vaSql += " AND rq.request_number = '" + requestNumber + "'";
   					}
   					if (!startDate.equals("")) {
   						vaSql += " AND rq.insert_date >= '" + startDate + "'";
   					} 
   					if (!toDate.equals("")) {
   						vaSql += " AND rq.insert_date <= '" + toDate + "'";
   					} 
   					if (!status.equals("")) {
   						vaSql += " AND rq.status IN (" + status + ")";
   					}
   					vaSql += " AND ROWNUM <= " + pnEnd + " AND ROWNUM > " + pnStart + ""
   						+ " ORDER BY " + paSortColumn;
			List<Map<String, Object>> result = db.queryList(vaSql);
			return result;
   	}
   	
 // ดึงข้อมูล คำร้อง เพื่อสอบถามใบคำร้อง
   	public List<Map<String, Object>> findAllRequestByRequestNumber(int siteId, String requestNumber, String startDate, String toDate, String status, String paSortColumn) {
   		String vaSql = "SELECT substr(rq.insert_date,7,2) || '/' || substr(rq.insert_date,5,2) || '/' || substr(rq.insert_date,1,4) as insert_date, " 
   					+ "rq.request_number, rq.applicant_name, "
   					+ "rt.request_description, CASE rq.status WHEN '1' THEN 'รับคำร้อง' WHEN '2' THEN 'รับเงิน' WHEN '3' THEN 'ปิดคำร้อง' ELSE 'ยกเลิก' END AS status "
   					+ "FROM request rq "
   					+ "INNER JOIN request_type rt ON rq.request_type = rt.request_type "
   					+ "WHERE rq.site_id = " + siteId;
   					if (!requestNumber.equals("")) {
   						vaSql += " AND rq.request_number = '" + requestNumber + "'";
   					}
   					if (!startDate.equals("")) {
   						vaSql += " AND rq.insert_date >= '" + startDate + "'";
   					} 
   					if (!toDate.equals("")) {
   						vaSql += " AND rq.insert_date <= '" + toDate + "'";
   					} 
   					if (!status.equals("")) {
   						vaSql += " AND rq.status IN (" + status + ")";
   					}
   					vaSql += "ORDER BY " + paSortColumn;
			List<Map<String, Object>> result = db.queryList(vaSql);
			return result;
   	}
   	
	public String getRequestTypeByRequestNumber(int pnSiteId,String pnRequestNumber) {
   		String vaSql = "SELECT request_type FROM request WHERE site_id = ? AND request_number = ?";
   		Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnRequestNumber);
        if (result != null) {
            return  result.get("REQUEST_TYPE").toString();  
        } else {
            return null;
        }
	}
}
